﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhaoxiLearningWpf.DataAccess.DataEntity;
using System.Data;
using ZhaoxiLearningWpf.Common;
using System.Threading;

namespace ZhaoxiLearningWpf.DataAccess
{
    public class SQLAceess
    {
        private static SQLAceess sqlInstance;
        private static volatile object locker = new object();
        private SQLAceess() { }

        public static SQLAceess GetSQLInstance()
        {
            if (sqlInstance == null) {
                lock (locker) {
                    if (sqlInstance == null) {
                        sqlInstance = new SQLAceess();
                    }
                }
            }
            return sqlInstance;
        }

        SqlConnection sqlConnection;
        SqlCommand sqlCommand;
        SqlDataAdapter dataAdapter;

        private void Dispose()
        {
            if (sqlConnection != null) {
                sqlConnection.Dispose();
                sqlConnection = null;
            }
            if (sqlCommand != null) {
                sqlCommand.Dispose();
                sqlCommand = null;
            }
            if (dataAdapter != null) {
                dataAdapter.Dispose();
                dataAdapter = null;
            }
        }

        public bool DBConnection()
        {
            //    if (sqlConnection == null) {

            //        string sqlStr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            //        sqlConnection = new SqlConnection(sqlStr);
            //        try {
            //            sqlConnection.Open();
            //            return true;
            //        }
            //        catch {
            //            return false;
            //        }
            //    }
            //    return false;
            //}
            string sqlStr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            if (sqlConnection == null)
                sqlConnection = new SqlConnection(sqlStr);

            try {
                sqlConnection.Open();
                return true;
            }
            catch {
                return false;
            }
        }

        public UserEntity GetUserInfo(string userName, string password)
        {
            try {
                if (DBConnection()) {
                    string userSQL = "select * from db_user where user_name=@user and password=@pwd and is_validation=1";
                    dataAdapter = new SqlDataAdapter(userSQL, sqlConnection);
                    dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@user", SqlDbType.VarChar) { Value = userName });
                    dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@pwd", SqlDbType.VarChar) { Value = $"{password}@{userName}".GetMD5() });

                    DataTable dataTable = new DataTable();
                    int count = dataAdapter.Fill(dataTable);

                    if (count <= 0)
                        throw new Exception("用户名或密码错误！");

                    DataRow row = dataTable.Rows[0];

                    if (row.Field<Int32>("is_can_login") == 0)
                        throw new Exception("用户没有权限！");

                    UserEntity user = new UserEntity()
                    {
                        UserName = row.Field<string>("user_name"),
                        RealName = row.Field<string>("real_name"),
                        Password = row.Field<string>("password"),
                        Avatar = row.Field<string>("avatar"),
                        Gender = row.Field<Int32>("gender"),
                    };

                    return user;
                }

                return null;
            }
            catch (Exception ex) {
                return null;
            }
            finally {
                this.Dispose();
            }
        }

    }
}
